package com.atguigu.gmall.controller;

public class Newzhibiao {
    public static void main(String[] args) {
        -- 计算每月新用户次月留存率
        WITH new_users AS (
                SELECT user_id, MIN(order_date) AS first_order_date
                FROM orders
                GROUP BY user_id
                HAVING first_order_date BETWEEN '2024-01-01' AND '2024-01-31'
),
        retained_users AS (
                SELECT a.user_id
        FROM new_users a
        JOIN orders b ON a.user_id = b.user_id
        WHERE b.order_date BETWEEN '2024-02-01' AND '2024-02-28'
)
        SELECT COUNT(retained_users.user_id) / COUNT(new_users.user_id) AS retention_rate
        FROM new_users
        LEFT JOIN retained_users ON new_users.user_id = retained_users.user_id;

    }
}
